# -*- coding: utf-8 -*-

class FinanceController < ApplicationController
  before_filter :employee_authorize
  before_filter :sys_before_log
  before_filter :employee_action_hx
  before_filter :privilege_filter,:except=>[:salesCostView,:salesProfitView,:statisticsSalesProfit,:getResidualGrantByCustomerIdStrategyId,:getResidualReceivableByCustomerIdStrategyId]

  #分析决策
  def analysis

  end


  #统计销售出库成本，每次出库按当月出库成本计算。
  def statisticsSalesCost
    # 首先计算一下成本价。
    #Product.cost_price
    #begin
    start = params[:start]
    limit = params[:limit]
    #以入库时间分析出年，月
    calYear = params[:begin_date].split(/-/)[0]
    calMonth = params[:begin_date].split(/-/)[1].to_i
    conditionsStr = ''


    if params[:begin_date].to_s != ''
      if conditionsStr == ''
        conditionsStr = "dod.updated_at >= '#{params[:begin_date]}'"
      else
        conditionsStr = conditionsStr+" AND dod.updated_at >= '#{params[:begin_date]}'"
      end
    end

    if params[:end_date].to_s != ''
      if conditionsStr == ''
        conditionsStr = "dod.updated_at <= '#{params[:end_date]}'"
      else
        conditionsStr = conditionsStr+" AND dod.updated_at <= '#{params[:end_date]}'"
      end
    end

    if params[:customer_id].to_s != ''
      if conditionsStr == ''
        conditionsStr = "c.id = #{params[:customer_id]}"
      else
        conditionsStr = conditionsStr+" AND c.id = #{params[:customer_id]}"
      end
    end

    #显示产品
    if params[:product_id].to_s != ''
      if conditionsStr == ''
        conditionsStr = "dod.product_id = #{params[:product_id]}"
      else
        conditionsStr = conditionsStr+" AND dod.product_id = #{params[:product_id]}"
      end
    end

    auditingStatusConditionStr = "so.auditing_status not like '%#{Constants::APPLY}%'
                       AND so.auditing_status not like '%撤消%'
                       AND do.auditing_status not like '%#{Constants::APPLY}%'
                       AND do.auditing_status not like '%撤消%'
                       AND so.company_id = #{session[:company_id]}"

    if conditionsStr != ""
      conditionsStr = "where " + conditionsStr + " AND " + auditingStatusConditionStr
    else
      conditionsStr = "where " + auditingStatusConditionStr
    end

    saleDeliveries = SaleOrder.find(:all,:select=>"dod.id,do.order_number delivery_order_number,do.auditing_status delivery_auditing_status, dod.quantity ,do.action_date,so.order_number as sale_order_number,so.auditing_status as sale_auditing_status,c.name as customer_name,p.name as product_name,p.id product_id,p.number as product_number,cp.cost_price cost_price,ROUND(quantity*cp.cost_price,2) cost_price_amount,cp.tax_price,(dod.quantity * cp.tax_price) as tax_price_amount" ,:joins =>"so
        left join delivery_orders do on do.sale_order_id = so.id
        left join delivery_order_details dod on dod.delivery_order_id = do.id
        left join products p on p.id = dod.product_id
        left join cost_prices cp on cp.product_id = dod.product_id AND cp.cal_year = DATE_FORMAT(do.action_date,'%Y') AND cp.cal_month = DATE_FORMAT(do.action_date,'%c')
        left join departments d on d.id = so.department_id
        left join customers c on c.id = so.customer_id
        #{conditionsStr}")

    sumCostPriceAmount = 0.00
    sumTaxPriceAmount = 0.00
    if saleDeliveries.length > 0
      saleDeliveries.each do |deliveryOrder|
        sumCostPriceAmount = sumCostPriceAmount + deliveryOrder.cost_price_amount.to_f
        sumTaxPriceAmount = sumTaxPriceAmount + deliveryOrder.tax_price_amount.to_f
      end
    end
    #判断是输出报表，还是输出Json
    if params[:sub_type].nil?
      jsonStr="{totalProperty:"+saleDeliveries.length.to_s+",sum_cost_price_amount:#{sumCostPriceAmount},sum_tax_price_amount:#{sumTaxPriceAmount},root:"+saleDeliveries.to_json()+"}"
      logger.info jsonStr
      render :text=>jsonStr, :layout=>false
    else
      require 'spreadsheet'
      Spreadsheet.client_encoding = "UTF-8"
      bookString = StringIO.new
      book = Spreadsheet.open "#{Rails.root.to_s}/report_template/sales_cost_report.xls"
      i = 4
      sheet1 = book.worksheet 0
      sheet1[3,0] = "销售订单"
      sheet1[3,1] = "审批状态"
      sheet1[3,2] = "出库单"
      sheet1[3,3] = "审批状态"
      sheet1[3,4] = "客户"
      sheet1[3,5] = "产品"
      sheet1[3,6] = "编号"
      sheet1[3,7] = "数量"
      sheet1[3,8] = "成本单价"
      sheet1[3,9] = "成本总额"
      sheet1[3,10] = "含税单价"
      sheet1[3,11] = "含税总额"
      sheet1[3,12] = "出库日期"

      format = Spreadsheet::Format.new :color => :blue,
        :weight => :bold,
        :size => 18

      sheet1.default_format = format
      saleDeliveries.each do |saleDelivery|
        sheet1[i,0] = saleDelivery.sale_order_number
        sheet1[i,1] = saleDelivery.sale_auditing_status
        sheet1[i,2] = saleDelivery.delivery_order_number
        sheet1[i,3] = saleDelivery.delivery_auditing_status
        sheet1[i,4] = saleDelivery.customer_name
        sheet1[i,5] = saleDelivery.product_name
        sheet1[i,6] = saleDelivery.product_number
        sheet1[i,7] = saleDelivery.quantity
        sheet1[i,8] = saleDelivery.cost_price
        sheet1[i,9] = saleDelivery.cost_price_amount
        sheet1[i,10] = saleDelivery.tax_price
        sheet1[i,11] = saleDelivery.tax_price_amount
        sheet1[i,12] = saleDelivery.action_date.to_s
        i += 1
      end

      sheet1[i,9] = sumCostPriceAmount
      sheet1[i,11] = sumTaxPriceAmount

      book.write bookString
      send_data bookString.string,:filename => '销售成本分析.xls'
    end
  end


  #统计销售利润分析
  def statisticsSalesProfit
    # 首先计算一下成本价。
    #Product.cost_price
    #begin
    start = params[:start]
    limit = params[:limit]
    #以入库时间分析出年，月
    calYear = params[:begin_date].split(/-/)[0]
    calMonth = params[:begin_date].split(/-/)[1].to_i
    conditionsStr = ''


    if params[:begin_date].to_s != ''
      if conditionsStr == ''
        conditionsStr = "dod.updated_at >= '#{params[:begin_date]}'"
      else
        conditionsStr = conditionsStr+" AND dod.updated_at >= '#{params[:begin_date]}'"
      end
    end

    if params[:end_date].to_s != ''
      if conditionsStr == ''
        conditionsStr = "dod.updated_at <= '#{params[:end_date]}'"
      else
        conditionsStr = conditionsStr+" AND dod.updated_at <= '#{params[:end_date]}'"
      end
    end

    if params[:customer_id].to_s != ''
      if conditionsStr == ''
        conditionsStr = "c.id = #{params[:customer_id]}"
      else
        conditionsStr = conditionsStr+" AND c.id = #{params[:customer_id]}"
      end
    end

    #显示产品
    if params[:product_id].to_s != ''
      if conditionsStr == ''
        conditionsStr = "dod.product_id = #{params[:product_id]}"
      else
        conditionsStr = conditionsStr+" AND dod.product_id = #{params[:product_id]}"
      end
    end

    auditingStatusConditionStr = "so.auditing_status not like '%#{Constants::APPLY}%'
                       AND so.auditing_status not like '%撤消%'
                       AND do.auditing_status not like '%#{Constants::APPLY}%'
                       AND do.auditing_status not like '%撤消%'
                       AND so.company_id = #{session[:company_id]}"

    if conditionsStr != ""
      conditionsStr = "where " + conditionsStr + " AND " + auditingStatusConditionStr
    else
      conditionsStr = "where " + auditingStatusConditionStr
    end

    saleDeliveries = SaleOrder.find(:all,:select=>"dod.id,do.order_number delivery_order_number,do.auditing_status delivery_auditing_status, dod.quantity ,do.action_date,so.order_number as sale_order_number,so.auditing_status as sale_auditing_status,c.name as customer_name,p.name as product_name,p.id product_id,p.number as product_number,cp.cost_price cost_price,ROUND(dod.quantity*cp.cost_price,2) cost_price_amount,cp.tax_price,(dod.quantity * cp.tax_price) as tax_price_amount,(sod.unit_price*sod.discount) sale_unit_price,(sod.unit_price*sod.discount-cp.cost_price) profit_price,(sod.unit_price*sod.discount-cp.tax_price) tax_profit_price,(dod.quantity * (sod.unit_price*sod.discount-cp.cost_price)) as profit_amount,(dod.quantity * (sod.unit_price*sod.discount-cp.tax_price)) as tax_profit_amount" ,:joins =>"so
        left join delivery_orders do on do.sale_order_id = so.id
        left join delivery_order_details dod on dod.delivery_order_id = do.id
        left join products p on p.id = dod.product_id
        left join cost_prices cp on cp.product_id = dod.product_id AND cp.cal_year = DATE_FORMAT(do.action_date,'%Y') AND cp.cal_month = DATE_FORMAT(do.action_date,'%c')
        left join departments d on d.id = so.department_id
        left join customers c on c.id = so.customer_id
        left join sale_order_details sod on sod.sale_order_id = so.id AND sod.product_id = dod.product_id
        #{conditionsStr}")

    sumCostPriceAmount = 0.00
    sumTaxPriceAmount = 0.00
    sumProfitAmount = 0.00
    sumTaxProfitAmount = 0.00
    if saleDeliveries.length > 0
      saleDeliveries.each do |saleDelivery|
        sumCostPriceAmount += saleDelivery.cost_price_amount.to_f
        sumTaxPriceAmount += saleDelivery.tax_price_amount.to_f
        sumProfitAmount += saleDelivery.profit_amount.to_f
        sumTaxProfitAmount += saleDelivery.tax_profit_amount.to_f
      end
    end
    #判断是输出报表，还是输出Json
    if params[:sub_type].nil?
      jsonStr="{totalProperty:"+saleDeliveries.length.to_s+",sum_cost_price_amount:#{sumCostPriceAmount},sum_tax_price_amount:#{sumTaxPriceAmount},sum_profit_amount:#{sumProfitAmount},sum_tax_profit_amount:#{sumTaxProfitAmount},root:"+saleDeliveries.to_json()+"}"
      logger.info jsonStr
      render :text=>jsonStr, :layout=>false
    else
      require 'spreadsheet'
      Spreadsheet.client_encoding = "UTF-8"
      bookString = StringIO.new
      book = Spreadsheet.open "#{Rails.root.to_s}/report_template/sales_cost_report.xls"
      i = 4
      sheet1 = book.worksheet 0
      sheet1[3,0] = "销售订单"
      sheet1[3,1] = "审批状态"
      sheet1[3,2] = "出库单"
      sheet1[3,3] = "审批状态"
      sheet1[3,4] = "客户"
      sheet1[3,5] = "产品"
      sheet1[3,6] = "编号"
      sheet1[3,7] = "数量"
      sheet1[3,8] = "成本单价"
      sheet1[3,9] = "成本总额"
      sheet1[3,10] = "含税单价"
      sheet1[3,11] = "含税总额"
      sheet1[3,12] = "出库日期"

      format = Spreadsheet::Format.new :color => :blue,
        :weight => :bold,
        :size => 18

      sheet1.default_format = format
      saleDeliveries.each do |saleDelivery|
        sheet1[i,0] = saleDelivery.sale_order_number
        sheet1[i,1] = saleDelivery.sale_auditing_status
        sheet1[i,2] = saleDelivery.delivery_order_number
        sheet1[i,3] = saleDelivery.delivery_auditing_status
        sheet1[i,4] = saleDelivery.customer_name
        sheet1[i,5] = saleDelivery.product_name
        sheet1[i,6] = saleDelivery.product_number
        sheet1[i,7] = saleDelivery.quantity
        sheet1[i,8] = saleDelivery.cost_price
        sheet1[i,9] = saleDelivery.cost_price_amount
        sheet1[i,10] = saleDelivery.tax_price
        sheet1[i,11] = saleDelivery.tax_price_amount
        sheet1[i,12] = saleDelivery.action_date.to_s
        i += 1
      end

      sheet1[i,9] = sumCostPriceAmount
      sheet1[i,11] = sumTaxPriceAmount

      book.write bookString
      send_data bookString.string,:filename => '销售利润分析.xls'
    end
  end

  #得到某个用户下的所有赠款。
  def getResidualReceivableByCustomerIdStrategyId
    receivables = Receivable.where("customer_id = ? AND auditing_status not like ? AND auditing_status not like ? AND sales_strategy_id = ?",params[:customer_id],Constants::APPLY,Constants::PASS_APPLY_CANCEL,params[:sales_strategy_id]).select("sum(amount) amount")
    sumAmount = 0
    if receivables[0].amount.nil?
      sumAmount = 0
    else
      sumAmount = receivables[0].amount
    end
    logger.info sumAmount
    jsonStr="{sum_amount:#{sumAmount}}"
    logger.info jsonStr
    render :text=>jsonStr, :layout=>false
  end
  
  #得到某个用户下的所有赠款。
  def getResidualGrantByCustomerIdStrategyId
    grants = Grant.joins("g left join receivables r on g.receivable_id = r.id").where("r.customer_id = ? AND r.auditing_status not like ? AND r.auditing_status not like ? AND g.auditing_status not like ? AND g.auditing_status not like ? AND g.sales_strategy_id = ?",params[:customer_id],Constants::APPLY,Constants::PASS_APPLY_CANCEL,Constants::APPLY,Constants::PASS_APPLY_CANCEL,params[:sales_strategy_id]).select("sum(r.amount) amount")
    sumAmount = 0
    if grants[0].amount.nil?
      sumAmount = 0
    else
      sumAmount = grants[0].amount
    end
    logger.info sumAmount
    jsonStr="{sum_amount:#{sumAmount}}"
    logger.info jsonStr
    render :text=>jsonStr, :layout=>false
  end
end
